-- 用户变量
SET @username = 9527;
SET @username = "10086";
-- 输出变量
SELECT @username;


-- 系统变量
-- 全局变量

SHOW GLOBAL VARIABLES;

-- 会话变量

show session variables;


-- 局部变量
-- 先使用存储过程来给局部变量提供环境
-- 局部变量的定义和使用
-- 存储过程的定义和使用

-- 存储过程的定义
CREATE PROCEDURE 存储过程名()
BEGIN
-- 一些sql语句
END
-- 存储过程使用
CALL 存储过程名();
-- 删除存储过程
DROP PROCEDURE 存储过程名;

CREATE PROCEDURE pro_declare_variable ()
BEGIN 
-- 局部变量的定义与使用
-- 方式一
-- DECLARE  变量名 数据类型  [DEFAULT  默认值]
DECLARE username VARCHAR(30) DEFAULT "9527";
-- 方式2 将select语句的结果赋给局部变量
DECLARE age TINYINT ;
-- 将学生表中的最大年龄赋给 age
SELECT MAX(sage) INTO age  FROM student; 
-- 输出变量
SELECT age;
-- 修改
SET username="周星星";
SELECT username;

END  
CALL pro_declare_variable();
DROP PROCEDURE pro_declare_variable;



-- 分支  IF

-- 查询女生中最小年龄，并根据年龄输出 小学生(0-17) 成年人(18-59) 老年人(>=60) 

-- 定义局部变量保存年龄
CREATE PROCEDURE  pro_if()
BEGIN
-- 定义变量
DECLARE minage TINYINT ;
-- 赋值
SELECT MIN(sage) INTO minage FROM student WHERE ssex ="女";
-- 判断年龄

IF minage >0 AND minage <18 THEN
SELECT "小萝莉";
ELSEIF minage >17 AND minage <60 THEN
SELECT "小姐姐";
ELSE 
SELECT "阿姨";
end IF;


END

CALL pro_if; 


-- 输出从1加到100的和

CREATE PROCEDURE pro_while()
BEGIN
DECLARE i INT  DEFAULT 1;
DECLARE total INT DEFAULT 0;

WHILE i<=100 DO
SET total=total +i;
SET i = i +1;
end WHILE;
SELECT total;

-- js
-- 
-- var i=1;
-- var total =0;
-- while (i<=100){
-- total = total +i;
-- i++;
-- }
END
CALL pro_while;

-- 输出1到10   输出5时终止循环
-- leave 终止指定循环
drop PROCEDURE pro_while_leave;
CREATE PROCEDURE pro_while_leave()
BEGIN
DECLARE i INT  DEFAULT 1;

outs:WHILE i<=10 DO

SELECT i;
-- IF i=5 then
--  leave outs;
-- END if;

SET i = i+1;

END WHILE;
END
CALL pro_while_leave;




-- 存储过程 

-- 不带参数的

-- 存储过程的定义
CREATE PROCEDURE 存储过程名()
BEGIN
-- 一些sql语句
END
-- 存储过程使用
CALL 存储过程名();
-- 删除存储过程
DROP PROCEDURE 存储过程名;


-- 带参数的 (输入和输出可以单独写其中一个，也可以都写，或者多写)
CREATE PROCEDURE 存储过程名(IN 变量名 数据类型,OUT 变量名 数据类型)
BEGIN

END

CALL 存储过程名(实际参数1，实际参数2)

-- 定义一个存储过程，传入一个int性的数据，表示年龄，找到成绩表中该年龄所对应学生的最高成绩，并返回该成绩。

DROP PROCEDURE pro_para;
CREATE PROCEDURE pro_para(IN age INT,OUT maxscore INT)
BEGIN

SELECT MAX(grade) INTO maxscore FROM score WHERE snum IN (SELECT snum FROM student WHERE sage = age);


END

CALL pro_para(23,@score);

SELECT @score;

-- 定义一个存储过程，传入一个varchar性的数据，表示学号，获得该学生的平均成绩，并返回该成绩。
	use schoolinfo;
	DROP PROCEDURE pro_sno;
	CREATE PROCEDURE pro_sno (IN num VARCHAR(10),OUT avg FLOAT)
	BEGIN
		SELECT AVG(score) INTO avg FROM sc WHERE sno =num;
	END

	CALL pro_sno("s001",@avg);
	SELECT @avg;



-- 触发器

-- 性别检查问题
-- 给学生表插入数据时，检查性别数据是否合理，如果 不为男和女，则默认为男。

-- 定义触发器
CREATE TRIGGER tri_student_insert  BEFORE INSERT  ON student FOR EACH ROW
BEGIN
-- 触发器用new 来代替将要插入或者更新的数据
   IF new.ssex !="男" AND new.ssex!="女" THEN
   SET new.ssex="男";
   END IF;
END
-- 删除触发器
DROP TRIGGER tri_student_insert;
insert into student VALUES ("s222","吴彦祖",34,"人");

-- 检查输入数据 性别和年龄。如果年龄<0 或者>120 默认为0 ，性别 默认为男

CREATE TRIGGER tri_student_insert BEFORE INSERT ON student FOR EACH ROW
BEGIN
    -- 性别
    IF new.ssex !="男" AND new.ssex!="女" THEN
    SET new.ssex="男";
    END IF;
    -- 年龄
    IF new.sage <0 OR new.sage >127 THEN
    SET new.sage =0;
    END IF;
END
insert into student VALUES ("s333","吴彦祖",-126,"人");


-- 更新 
-- 对于更新 new 指代更新后的数据， old指代更新之前的数据
-- 更新学生表，确保 年龄和性别更新后数据是合理的。不合理则设置为默认值


CREATE TRIGGER tri_student_update BEFORE UPDATE ON student FOR EACH ROW
BEGIN
    -- 性别
    IF new.ssex !="男" AND new.ssex!="女" THEN
    SET new.ssex="男";
    END IF;
    -- 年龄
    IF new.sage <0 OR new.sage >127 THEN
    SET new.sage =0;
    END IF;
END



-- [扩展]
-- 更新学生表，确保 年龄和性别更新后数据是合理的。不合理则先判断以前的值是否合理，如果合理则使用更新之前的，否则就使用默认值
CREATE TRIGGER tri_student_update BEFORE UPDATE ON student FOR EACH ROW
BEGIN
-- 年龄
 if new.sage <0 OR new.sage >127 THEN
   IF old.sage >=0 AND old.sage <=127 THEN
    SET new.sage = old.sage;
   else 
    SET new.sage = 0;
   end IF;
 END IF;



END
drop TRIGGER tri_student_update;
UPDATE student SET sage = -1 where sname = "吴彦祖";



use schoolinfo;
-- 事务
-- 开启一个事务											
begin;
INSERT INTO student values ("s333","二营长",34,"人");
update student SET sno ="s1111" WHERE sss = 0;

ROLLBACK;
COMMIT;



-- 条件处理器


-- 创建存储过程，里面创建事务，事务里向学生表添加数据，如果添加失败，则回滚。
-- 利用条件处理器来捕获错误(异常),发生异常时事务回滚。

DROP PROCEDURE pro_transaction;
CREATE PROCEDURE pro_transaction()
BEGIN
-- 定义条件处理器
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION ROLLBACK;

-- 开启一个事务
START TRANSACTION;
-- 插入数据
INSERT INTO student values("ssss","王思聪",32,"妖");
-- 模拟发生错误
-- UPDATE student set sss = 0;
-- 提交事务
commit;

END

CALL pro_transaction;


-- 练习：创建存储过程，开启一个事务，里面向成绩表添加数据，如果添加失败，则回滚。
DROP PROCEDURE pro_transaction;
CREATE PROCEDURE pro_transaction()
BEGIN
-- 定义条件处理器
DECLARE iserror INT DEFAULT 0; -- 定义变量表示是否发生错误，0表示没有，1表示发生异常
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET iserror = 1 ;
-- 开启一个事务
START TRANSACTION;

INSERT INTO sc VALUES ('s111',"c001",22);
-- iserror 为1 则发生异常，回滚
if iserror = 1 THEN
ROLLBACK;
END IF;


COMMIT;


END
CALL pro_transaction;




-- 游标

-- 查找学生表所有的数据
SELECT * FROM student;

-- 基本使用
-- 定义游标，查询学生表中的所有数据并将结果集保存在游标中，输出第一条数据
CREATE PROCEDURE pro_cursor()
BEGIN
-- 定义变量用于接受游标的数据
DECLARE sname VARCHAR(20) ;
DECLARE sno VARCHAR(20);
DECLARE sage INT ;
DECLARE ssex char(2);

-- 定义游标
DECLARE cur_student CURSOR  FOR SELECT * FROM student;

-- 打开游标
OPEN cur_student;

-- 取值
FETCH cur_student INTO sno,sname,sage,ssex;

-- 输出
SELECT sno,sname,sage,ssex;

-- 关闭游标
CLOSE cur_student;

END

CALL pro_cursor;






use schoolinfo;
-- 定义游标，查询学生表中的所有数据并将结果集保存在游标中，输出里面的每一条数据
DROP PROCEDURE pro_cursor;
CREATE PROCEDURE pro_cursor()
BEGIN
-- 定义变量用于接受游标的数据
DECLARE sname VARCHAR(20) ;
DECLARE sno VARCHAR(20);
DECLARE sage INT ;
DECLARE ssex char(2);
DECLARE iserror INT DEFAULT 0;-- 保存异常是否出现，0表示没有异常
-- 定义游标
DECLARE cur_student CURSOR  FOR SELECT * FROM student;
-- 定义 条件处理器 
 DECLARE CONTINUE HANDLER FOR NOT found SET iserror = 1;
-- 打开游标
OPEN cur_student;


-- 死循环
while iserror=0 DO
-- 取值
FETCH cur_student INTO sno,sname,sage,ssex;
SELECT sno,sname,sage,ssex;
-- 输出
END WHILE;
-- 关闭游标
CLOSE cur_student;
END

CALL pro_cursor;





